import express from 'express'
import mysql from 'mysql'
//创建连接
const connection = mysql.createConnection({
	host     : 'localhost',
	user     : 'root',
	password : 'root',
	database : 'spring01'
});
//执行创建连接 
connection.connect();


const UserModel = {};

/*
    -------------------- 分割线 --------------------
*/

//查询列表--SQL
const sql_queryUserList = (params) => {
    let sql = `SELECT
        t1.primary_id as primaryId,
        t1.user_id as userId,
        t1.user_name as userName,
        t1.nick_name as nickName,
        t1.sex as sex
        FROM spring01_user_info t1
        WHERE 1=1`;

    // for(let key in params) {
    //     let value = params[key];
    //     if(value) {
    //         sql += ` and ${key}='${value}'`;
    //     }
    // }

    return sql;
};
//查询列表--执行查询
UserModel.queryUserList = ((params) => {
	return new Promise(function (resolve, reject) {
        connection.query(sql_queryUserList(params), {},function (err, data) {
            if (err) {
                reject(err);
            }else {
                resolve(data);
            }
        });
    });
});

/*
    -------------------- 分割线 --------------------
*/

//查询详情--SQL
const sql_getUserItemById = (primaryId) => {
    return `SELECT
        t1.primary_id as primaryId,
        t1.user_id as userId,
        t1.user_name as userName,
        t1.nick_name as nickName,
        t1.sex as sex
        FROM spring01_user_info t1
        WHERE primary_Id = "${primaryId}"`;
};
//查询详情--执行查询
UserModel.getUserItemById = ((params) => {
    return new Promise(function (resolve, reject) {
        connection.query(sql_getUserItemById(params.primaryId), {},function (err, data) {
            if (err) {
                reject(err);
            }else {
                resolve(data[0] || {});
            }
        });
    });
});

//新增--SQL
const sql_addUserItem = (params) => {
    return `INSERT InTO
            spring01_user_info(primary_id, user_id, user_name, nick_name, sex, password)
        values
            ("${params.primaryId}","${params.userId}","${params.userName}","${params.nickName}","${params.sex}","${params.password}")`;
};
//编辑--执行查询
UserModel.addUserItem = ((params) => {
    return new Promise(function (resolve, reject) {
        connection.query(sql_addUserItem(params), {},function (err, data) {
            if (err) {
                reject(err);
            }else {
                resolve(data || {});
            }
        });
    });
});

//编辑--SQL
const sql_editUserItem = (params) => {
    return `UPDATE spring01_user_info
        SET user_name = "${params.userName}",
            nick_name = "${params.nickName}",
            sex = "${params.sex}"
        WHERE primary_id = "${params.primaryId}"`;
};
//编辑--执行查询
UserModel.editUserItem = ((params) => {
    return new Promise(function (resolve, reject) {
        connection.query(sql_editUserItem(params), {},function (err, data) {
            if (err) {
                reject(err);
            }else {
                resolve(data || {});
            }
        });
    });
});

//删除--SQL
const sql_delUserItem = (params) => {
    return `DELETE FROM spring01_user_info
        WHERE primary_id = "${params.primaryId}"`;
};
//删除--执行查询
UserModel.delUserItem = ((params) => {
    return new Promise(function (resolve, reject) {
        connection.query(sql_delUserItem(params), {},function (err, data) {
            if (err) {
                reject(err);
            }else {
                resolve(data || {});
            }
        });
    });
});

//登录--SQL
const sql_loginIn = (params) => {
    return `SELECT * FROM spring01_user_info
        WHERE user_id = "${params.userId}"
          AND password = "${params.password}"`;
};
//登录--执行查询
UserModel.loginIn = ((params) => {
    return new Promise(function (resolve, reject) {
        connection.query(sql_loginIn(params), {},function (err, data) {
            if (err) {
                reject(err);
            }else {
                resolve(data || {});
            }
        });
    });
});

export default UserModel